{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Musicians - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "**List the name and town of birth of any performer born in the same city as James First.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>m_name</th>\n",
       "        <th>place_town</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alan Fluff</td>\n",
       "        <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andy Jones</td>\n",
       "        <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Theo Mengel</td>\n",
       "        <td>London</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Alan Fluff', 'London'), ('Andy Jones', 'London'), ('Theo Mengel', 'London')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT born_in FROM musician \n",
    "        WHERE m_name='James First'\n",
    ")\n",
    "SELECT m_name, place_town\n",
    "  FROM musician JOIN place ON (\n",
    "      musician.born_in=place.place_no) JOIN t ON (\n",
    "      musician.born_in=t.born_in)\n",
    "    WHERE m_name != 'James First'\n",
    "    ORDER BY m_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "**Create a list showing for EVERY musician born in Britain the number of compositions and the number of instruments played.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>m_name</th>\n",
       "        <th>n_comp</th>\n",
       "        <th>n_instrm</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alan Fluff</td>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andy Jones</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Davis Heavan</td>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fred Bloggs</td>\n",
       "        <td>0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harriet Smithson</td>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harry Forte</td>\n",
       "        <td>3</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James First</td>\n",
       "        <td>1</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James Steeple</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Louise Simpson</td>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lovely Time</td>\n",
       "        <td>0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Phil Hot</td>\n",
       "        <td>0</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Steven Chaytors</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Theo Mengel</td>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Alan Fluff', 2, 1),\n",
       " ('Andy Jones', 0, 1),\n",
       " ('Davis Heavan', 3, 1),\n",
       " ('Fred Bloggs', 0, 2),\n",
       " ('Harriet Smithson', 2, 1),\n",
       " ('Harry Forte', 3, 2),\n",
       " ('James First', 1, 4),\n",
       " ('James Steeple', 0, 1),\n",
       " ('Louise Simpson', 3, 1),\n",
       " ('Lovely Time', 0, 2),\n",
       " ('Phil Hot', 0, 3),\n",
       " ('Steven Chaytors', 0, 1),\n",
       " ('Theo Mengel', 3, 1)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "  SELECT m_no, m_name, COUNT(*) n_instrm FROM\n",
    "    musician LEFT JOIN place ON (\n",
    "        musician.born_in=place.place_no) LEFT JOIN\n",
    "    composer ON (\n",
    "        composer.comp_is=musician.m_no) LEFT JOIN\n",
    "    has_composed ON (\n",
    "        has_composed.cmpr_no=composer.comp_no) LEFT JOIN\n",
    "    composition ON (\n",
    "        has_composed.cmpn_no=composition.c_no)\n",
    "    WHERE place_country IN ('England', 'Scotland')\n",
    "    GROUP BY m_no, m_name\n",
    ")\n",
    "SELECT t.m_name, COUNT(perf_is) n_comp, t.n_instrm\n",
    "  FROM t LEFT JOIN performer ON (t.m_no=performer.perf_is)\n",
    "    GROUP BY t.m_name, t.n_instrm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "**Give the band name, conductor and contact of the bands performing at the most recent concert in the Royal Albert Hall.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>band_name</th>\n",
       "        <th>contact</th>\n",
       "        <th>conductor</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Somebody Loves this</td>\n",
       "        <td>Theo Mengel</td>\n",
       "        <td>Alan Fluff</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Somebody Loves this', 'Theo Mengel', 'Alan Fluff')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT band_name, a.m_name contact, b.m_name conductor\n",
    "    FROM band JOIN musician a ON (band.band_contact=a.m_no) JOIN\n",
    "    performance ON (performance.gave=band.band_no) JOIN\n",
    "    musician b ON (performance.conducted_by=b.m_no) JOIN\n",
    "    (SELECT * FROM concert WHERE concert_venue='Royal Albert Hall' \n",
    "     ORDER BY con_date DESC limit 1) AS concert ON (\n",
    "        concert.concert_no=performance.performed_in)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "**Give a list of musicians associated with Glasgow. Include the name of the musician and the nature of the association - one or more of 'LIVES_IN', 'BORN_IN', 'PERFORMED_IN' AND 'IN_BAND_IN'.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>m_name</th>\n",
       "        <th>assoc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alan Fluff</td>\n",
       "        <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andy Jones</td>\n",
       "        <td>BORN_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Davis Heavan</td>\n",
       "        <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Elsie James</td>\n",
       "        <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Freda Miles</td>\n",
       "        <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harriet Smithson</td>\n",
       "        <td>BORN_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James Steeple</td>\n",
       "        <td>BORN_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jeff Dawn</td>\n",
       "        <td>BORN_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jeff Dawn</td>\n",
       "        <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Louise Simpson</td>\n",
       "        <td>BORN_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Louise Simpson</td>\n",
       "        <td>LIVES_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lovely Time</td>\n",
       "        <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lovely Time</td>\n",
       "        <td>LIVES_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Steven Chaytors</td>\n",
       "        <td>LIVES_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tony Smythe</td>\n",
       "        <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Alan Fluff', 'IN_BAND_IN'),\n",
       " ('Andy Jones', 'BORN_IN'),\n",
       " ('Davis Heavan', 'IN_BAND_IN'),\n",
       " ('Elsie James', 'IN_BAND_IN'),\n",
       " ('Freda Miles', 'IN_BAND_IN'),\n",
       " ('Harriet Smithson', 'BORN_IN'),\n",
       " ('James Steeple', 'BORN_IN'),\n",
       " ('Jeff Dawn', 'BORN_IN'),\n",
       " ('Jeff Dawn', 'IN_BAND_IN'),\n",
       " ('Louise Simpson', 'BORN_IN'),\n",
       " ('Louise Simpson', 'LIVES_IN'),\n",
       " ('Lovely Time', 'IN_BAND_IN'),\n",
       " ('Lovely Time', 'LIVES_IN'),\n",
       " ('Steven Chaytors', 'LIVES_IN'),\n",
       " ('Tony Smythe', 'IN_BAND_IN')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t1 AS (\n",
    "  SELECT m_name, 'LIVES_IN' assoc, place_town\n",
    "    FROM musician JOIN place ON (musician.born_in=place.place_no)\n",
    "), t2 AS (\n",
    "  SELECT m_name, 'BORN_IN' assoc, place_town\n",
    "    FROM musician JOIN place ON (musician.living_in=place.place_no)\n",
    "), t3 AS (\n",
    "  SELECT m_name, 'PERFORMED_IN' assoc, place_town\n",
    "    FROM musician JOIN performer ON (\n",
    "        musician.m_no=performer.perf_is) JOIN\n",
    "    plays_in ON (performer.perf_no=plays_in.player) JOIN\n",
    "    performance ON (\n",
    "        performance.gave=plays_in.band_id) JOIN\n",
    "    concert ON (\n",
    "        concert.concert_no=performance.performed_in) JOIN\n",
    "    place ON (concert.concert_in=place.place_no)\n",
    "), t4 AS (\n",
    "  SELECT m_name, 'IN_BAND_IN' assoc, place_town\n",
    "    FROM musician JOIN plays_in ON (\n",
    "        musician.m_no=plays_in.player) JOIN\n",
    "    band ON (plays_in.band_id=band.band_no) JOIN\n",
    "    place ON (band.band_home=place.place_no)\n",
    ")\n",
    "SELECT DISTINCT m_name, assoc FROM\n",
    "  (SELECT * FROM t1 \n",
    "   UNION ALL\n",
    "   SELECT * FROM t2 \n",
    "   UNION ALL\n",
    "   SELECT * FROM t3 \n",
    "   UNION ALL\n",
    "   SELECT * FROM t4) AS a\n",
    "    WHERE place_town='Glasgow'\n",
    "    ORDER BY m_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "**Jeff Dawn plays in a band with someone who plays in a band with Sue Little. Who is it and what are the bands?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>m_name</th>\n",
       "        <th>jeff_dawn_band</th>\n",
       "        <th>sue_little_band</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Smith</td>\n",
       "        <td>AASO</td>\n",
       "        <td>BBSO</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('John Smith', 'AASO', 'BBSO')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t1 AS (\n",
    "  SELECT m_no, m_name, a.band_name FROM\n",
    "    musician JOIN performer ON (\n",
    "        musician.m_no=performer.perf_is) JOIN\n",
    "    plays_in ON (performer.perf_no=plays_in.player) JOIN\n",
    "    (SELECT band_id, band_name\n",
    "       FROM musician JOIN performer ON (\n",
    "           musician.m_no=performer.perf_is) JOIN\n",
    "       plays_in ON (performer.perf_no=plays_in.player) JOIN\n",
    "       band ON (plays_in.band_id=band.band_no)\n",
    "       WHERE m_name ='Sue Little') a \n",
    "    ON (a.band_id=plays_in.band_id)\n",
    "), t2 AS (\n",
    "  SELECT m_no, m_name, a.band_name FROM\n",
    "    musician JOIN performer ON (\n",
    "        musician.m_no=performer.perf_is) JOIN\n",
    "    plays_in ON (performer.perf_no=plays_in.player) JOIN\n",
    "    (SELECT band_id, band_name\n",
    "       FROM musician JOIN performer ON (\n",
    "           musician.m_no=performer.perf_is) JOIN\n",
    "       plays_in ON (performer.perf_no=plays_in.player) JOIN\n",
    "       band ON (plays_in.band_id=band.band_no)\n",
    "       WHERE m_name ='Jeff Dawn') a \n",
    "    ON (a.band_id=plays_in.band_id)\n",
    ")\n",
    "SELECT t2.m_name, t2.band_name jeff_dawn_band, \n",
    "    t1.band_name sue_little_band\n",
    "    FROM t2 JOIN t1 ON t2.m_no=t1.m_no"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
